﻿CREATE PROCEDURE Person.GetMailingLabels 
	-- Add the parameters for the stored procedure here
	@EventId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT
		CASE WHEN c.Id IS NOT NULL THEN (c.FirstName + ' ' + c.LastName) ELSE (f.Name) END AS Name,
		CASE WHEN c.Id IS NOT NULL THEN (caa.AddressLine1) ELSE (fa.AddressLine1) END AS Address1,
		CASE WHEN c.Id IS NOT NULL THEN (caa.AddressLine2) ELSE (fa.AddressLine2) END AS Address2,
		CASE WHEN c.Id IS NOT NULL 
			THEN (caa.City + ', ' + cps.StateProvinceCode + ' ' + caa.PostalCode) 
			ELSE (fa.City + ', ' + fps.StateProvinceCode + ' ' + fa.PostalCode) END AS CityStateZip
	FROM
		Event.Guest g
		LEFT JOIN Person.Contact c
			ON c.Id = g.GuestId AND g.GuestTypeId = 1
		LEFT JOIN Contact.Address ca
			ON c.Id = ca.ContactId AND ca.AddressTypeId = 1
		LEFT JOIN Person.Address caa
			ON ca.AddressId = caa.Id
		LEFT JOIN Person.StateProvince cps
			ON cps.Id = caa.StateProvinceId
		LEFT JOIN Contact.Family f
			ON f.Id = g.GuestId AND g.GuestTypeId = 2
		LEFT JOIN Person.Address fa
			ON f.AddressId = fa.Id
		LEFT JOIN Person.StateProvince fps
			ON fps.Id = fa.StateProvinceId
	WHERE
		g.EventId = @EventId
END